package com.nulp.psycho.db.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.nulp.psycho.db.ConnectionManager;
import com.nulp.psycho.db.dao.BaseDao;
import com.nulp.psycho.db.mapping.Converter;
import com.nulp.psycho.db.model.Interpretation;

public class InterpretationDao implements BaseDao{
	
	private final String ADD_INTERPRETATION = "INSERT INTO interpretation (test_id, param, start_value, finish_value, explanation, text) VALUES (?,?,?,?,?,?)";
	private final String GET_INTERPRETATION_BY_ID = "SELECT * FROM interpretation WHERE id = ?";
	private final String REMOVE_INTERPRETATION = "DELETE FROM interpretation WHERE id = ?";
	private final String UPDATE_INTERPRETATION = "UPDATE interpretation SET test_id=?, param=?, start_value=?, finish_value=?, explanation=?, text=? WHERE id = ?";
	
	private final String GET_INTERPRETATION_BY_PARAM_AND_VALUE = "SELECT * FROM interpretation i WHERE i.param = ? AND i.start_value <= ? AND i.finish_value>=?";
	
	@Override
	public <T> void create(T element) {
		Interpretation temp = (Interpretation) element;
		Connection connection = ConnectionManager.getInstance().getConnection();
		try (PreparedStatement preparedStatement = connection
				.prepareStatement(ADD_INTERPRETATION)) {
			preparedStatement.setInt(1, temp.getTestId());
			preparedStatement.setString(2, temp.getParam());
			preparedStatement.setInt(3, temp.getStartValue());
			preparedStatement.setInt(4, temp.getFinishValue());
			preparedStatement.setString(5, temp.getExplanation());
			preparedStatement.setString(6, temp.getText());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			ConnectionManager.getInstance().release(connection);
		}		
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> T read(Integer id) {
		Connection connection = ConnectionManager.getInstance().getConnection();
		Interpretation interpretation = null;
		try (PreparedStatement statement = connection
				.prepareStatement(GET_INTERPRETATION_BY_ID)) {
			statement.setInt(1, id);
			ResultSet rs = statement.executeQuery();
			if (rs.next())
				interpretation = Converter.<Interpretation> fromResaultSetToObject(rs,
						Interpretation.class);
		} catch (SQLException e) {
		} finally {
			ConnectionManager.getInstance().release(connection);
		}
		return (T) interpretation;	
	}

	@Override
	public void delete(Integer id) {
		Connection connection = ConnectionManager.getInstance().getConnection();
		try (PreparedStatement preparedStatement = connection
				.prepareStatement(REMOVE_INTERPRETATION)) {
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			ConnectionManager.getInstance().release(connection);
		}		
	}

	@Override
	public <T> void update(T element) {
		Interpretation temp = (Interpretation) element;
		Connection connection = ConnectionManager.getInstance().getConnection();
		try (PreparedStatement preparedStatement = connection
				.prepareStatement(UPDATE_INTERPRETATION)) {
			preparedStatement.setInt(7, temp.getId());
			preparedStatement.setInt(1, temp.getTestId());
			preparedStatement.setString(2, temp.getParam());
			preparedStatement.setInt(3, temp.getStartValue());
			preparedStatement.setInt(4, temp.getFinishValue());
			preparedStatement.setString(5, temp.getExplanation());
			preparedStatement.setString(6, temp.getText());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			ConnectionManager.getInstance().release(connection);
		}		
	}
	
	@SuppressWarnings("unchecked")
	public <T> T getInterpretationByValue(String paramName, Integer paramValue) {
		Connection connection = ConnectionManager.getInstance().getConnection();
		Interpretation interpretation = null;
		try (PreparedStatement statement = connection
				.prepareStatement(GET_INTERPRETATION_BY_PARAM_AND_VALUE)) {
			statement.setString(1, paramName);
			statement.setInt(2, paramValue);
			statement.setInt(3, paramValue);
			ResultSet rs = statement.executeQuery();
			if (rs.next())
				interpretation = Converter.<Interpretation> fromResaultSetToObject(rs,
						Interpretation.class);
		} catch (SQLException e) {
		} finally {
			ConnectionManager.getInstance().release(connection);
		}
		return (T) interpretation;	
	}
	
	
}
